﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Transactions;
using System.Xml.Serialization;

namespace Common.Utilities.DLR
{
    /// <summary>
    /// 
    /// </summary>
    public class OrmUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="objType"></param>
        /// <param name="keyFieldValue"></param>
        /// <returns></returns>
        public static object GetObjectByKey(string connStr, Type objType, string keyFieldValue)
        {
            string tblName = objType.Name;
            string keyFieldName = string.Empty;
            bool isIdentity = false;
            if (!OrmUtil.CheckTableExistance(connStr, objType, ref tblName, ref keyFieldName, ref isIdentity))
            {
                OrmUtil.CreateTable(connStr, objType, ref tblName, ref keyFieldName, ref isIdentity);
            }

            TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, keyFieldName);
            if (string.IsNullOrEmpty(keyCol.ColumnName))
                throw new Exception("Unable to get corresponding table");

            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(string.Format("select * from [{0}] where [{1}]={2}",
                    tblName, keyFieldName,
                    FieldDataType.ToMssqlDataValue(keyCol.DbType, keyFieldValue)),
                    conn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (dt.Rows.Count > 0)
            {
                List<object > objs = OrmUtil.GetObjFromTable(objType, dt);
                if (objs != null && objs.Count > 0)
                    return objs[0];
                else
                    return null;
            }
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="obj"></param>
        public static void Update(string connStr, ref object obj)
        {
            string tblName = obj.GetType().Name;
            string keyFieldName = string.Empty;
            bool isIdentity = false;
            if (!CheckTableExistance(connStr, obj.GetType(), ref tblName, ref keyFieldName, ref isIdentity))
            {
                CreateTable(connStr, obj.GetType(), ref tblName, ref keyFieldName, ref isIdentity);
            }

            TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, keyFieldName);
            if (string.IsNullOrEmpty(keyCol.ColumnName))
                throw new Exception("Unable to get corresponding table");
            string keyValue = GetSerializedObjFieldValue(obj, keyFieldName);
            if (string.IsNullOrEmpty(keyValue))
                throw new Exception("Unable to get key value");
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, tblName);

            SqlConnection conn = new SqlConnection(connStr);
            using (new TransactionScope())
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = string.Format("select count (*) from {0} where {1}={2}",
                                                    tblName, keyFieldName,
                                                    FieldDataType.ToMssqlDataValue(keyCol.DbType, keyValue));
                    bool exist = false;
                    try
                    {
                        int count = int.Parse(cmd.ExecuteScalar().ToString());
                        if (count > 0)
                            exist = true;
                    }
                    catch
                    {
                        exist = false;
                    }
                    string sql = string.Empty;
                    if (exist)
                    {
                        sql = "update " + tblName + " set ";
                        foreach (TableColumn col in cols)
                        {
                            if (col.ColumnName.ToLower() != keyFieldName.ToLower())
                            {
                                sql += col.ColumnName + "=" + FieldDataType.ToMssqlDataValue(col.DbType,
                                                                                             GetSerializedObjFieldValue
                                                                                                 (obj, col.ColumnName)) +
                                       ",";
                            }
                        }
                        if (sql.EndsWith(","))
                        {
                            sql = sql.Substring(0, sql.Length - 1);
                        }
                        sql += " where " + keyFieldName + "=" + FieldDataType.ToMssqlDataValue(keyCol.DbType,
                                                                                               GetSerializedObjFieldValue
                                                                                                   (obj, keyFieldName));
                    }
                    else
                    {
                        sql = "insert into " + tblName + "(";
                        foreach (TableColumn col in cols)
                        {
                            if (!col.IsIdentity)
                                sql += col.ColumnName + ",";
                        }
                        if (sql.EndsWith(","))
                        {
                            sql = sql.Substring(0, sql.Length - 1);
                        }
                        sql += ") values (";
                        foreach (TableColumn col in cols)
                        {
                            if (!col.IsIdentity)
                            {
                                sql +=
                                    FieldDataType.ToMssqlDataValue(col.DbType,
                                                                   GetSerializedObjFieldValue(obj, col.ColumnName)) +
                                    ",";
                            }
                        }
                        if (sql.EndsWith(","))
                        {
                            sql = sql.Substring(0, sql.Length - 1);
                        }
                        sql += ")";
                    }
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    if (isIdentity)
                    {
                        // cmd.CommandText = "select max (" + keyFieldName + ") from " + tblName;
                        cmd.CommandText = "SELECT @@IDENTITY";
                        int pkValue = int.Parse(cmd.ExecuteScalar().ToString());
                        MemberInfo pkMemberInfo = GetPkFieldMemberInfo(obj.GetType());
                        if (pkMemberInfo != null)
                        {
                            if (pkMemberInfo.MemberType == MemberTypes.Field)
                            {
                                ((FieldInfo)pkMemberInfo).SetValue(obj, pkValue);
                            }
                            else if (pkMemberInfo.MemberType == MemberTypes.Property)
                            {
                                ((PropertyInfo)pkMemberInfo).SetValue(obj, pkValue, null);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="obj"></param>
        public static void Delete(string connStr, object obj)
        {
            string tblName = obj.GetType().Name;
            string pkFieldName = string.Empty;
            bool isIdentity = false;
            if (!CheckTableExistance(connStr, obj.GetType(), ref tblName, ref pkFieldName, ref isIdentity))
            {
                CreateTable(connStr, obj.GetType(), ref tblName, ref pkFieldName, ref isIdentity);
            }
            MemberInfo pkMember = GetPkFieldMemberInfo(obj.GetType());
            string pkValue = string.Empty;
            if (pkMember.MemberType == MemberTypes.Field)
            {
                pkValue = ((FieldInfo)pkMember).GetValue(obj).ToString();
            }
            else if (pkMember.MemberType == MemberTypes.Property)
            {
                pkValue = ((PropertyInfo)pkMember).GetValue(obj, null).ToString();
            }

            SqlConnection conn = new SqlConnection(connStr);
            using (new TransactionScope())
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    TableColumn keyCol = DbUtil.GetTableColumn(connStr, tblName, pkFieldName);
                    if (string.IsNullOrEmpty(keyCol.ColumnName))
                        throw new Exception("Unable to get corresponding table");

                    cmd.CommandText = string.Format("delete {0} where {1}={2}",
                                                    tblName, keyCol.ColumnName,
                                                    FieldDataType.ToMssqlDataValue(keyCol.DbType, pkValue));
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        #region table -> obj
        /// <summary>
        /// 
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="dtData"></param>
        /// <returns></returns>
        private static List<object> GetObjFromTable(Type objType, DataTable dtData)
        {
            List<object> coll = new List<object>();
            if (dtData != null && dtData.Rows.Count > 0)
            {
                foreach (DataRow dr in dtData.Rows)
                {
                    object instance = Activator.CreateInstance(objType);

                    object temp = (object)instance;

                    FieldInfo[] fields = objType.GetFields();
                    foreach (FieldInfo field in fields)
                    {
                        string columnName = field.Name;
                        object[] customAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (customAttrs != null && customAttrs.Length == 1)
                        {
                            OrmFieldAttribute fieldAtt = (OrmFieldAttribute)customAttrs[0];
                            columnName = fieldAtt.ColumnName;
                        }

                        if (field.IsStatic == false && dtData.Columns.Contains(columnName))
                        {
                            object fieldValue = dr[columnName];
                            if (fieldValue is DBNull || fieldValue == null)
                            {
                                fieldValue = null;
                            }
                            else
                            {
                                fieldValue = OrmUtil.InstantiateFieldValue(objType, field.Name, fieldValue.ToString());
                            }
                            if (fieldValue != DBNull.Value && fieldValue != null && fieldValue.GetType() != field.FieldType)
                            {
                                fieldValue = Convert.ChangeType(fieldValue, field.FieldType);
                            }
                            if (!(fieldValue is DBNull))
                            {

                                field.SetValue(temp, fieldValue);
                            }
                        }
                    }
                    PropertyInfo[] props = objType.GetProperties();
                    if (props != null && props.Length > 0)
                    {
                        foreach (PropertyInfo prop in props)
                        {
                            string columnName = prop.Name;
                            object[] customAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                            if (customAttrs != null && customAttrs.Length == 1)
                            {
                                OrmFieldAttribute propAtt = (OrmFieldAttribute)customAttrs[0];
                                columnName = propAtt.ColumnName;
                            }
                            if (dtData.Columns.Contains(columnName))
                            {
                                object propValue = dr[columnName];
                                if (propValue == null || propValue is DBNull)
                                {
                                    propValue = null;
                                }
                                else
                                {
                                    propValue = OrmUtil.InstantiateFieldValue(objType, prop.Name, propValue.ToString());
                                }

                                if (propValue != DBNull.Value && propValue != null && propValue.GetType() != prop.PropertyType)
                                {
                                    propValue = Convert.ChangeType(propValue, prop.PropertyType);
                                }
                                if (!(propValue is DBNull))
                                {
                                    prop.SetValue(temp, propValue, null);
                                }
                            }
                        }
                    }

                    instance = temp;
                    coll.Add(instance);
                }
            }
            return coll;
        }
        #endregion

        #region schema
        private static bool CheckTableExistance(string connStr, Type type, ref string tblName, ref string pkFieldName, ref bool isIdentity)
        {
            object[] atts =
                type.GetCustomAttributes(typeof(OrmClassAttribute), false);
            OrmClassAttribute classAtt = null;
            if (atts != null && atts.Length > 0)
            {
                foreach (object att in atts)
                {
                    if (att is OrmClassAttribute)
                    {
                        classAtt = (OrmClassAttribute)att;
                        break;
                    }
                }
            }
            if (classAtt == null)
                throw new Exception("Object must be decorated with OrmClass attribute in order to use ORM");

            tblName = classAtt.TableName;
            pkFieldName = classAtt.PkFieldName;
            isIdentity = classAtt.IsPkIdentityField;

            return DbUtil.IsTableExist(DataProviderType.MSSQL, connStr, tblName);
        }

        private static void CreateTable(string connStr, Type type, ref string tblName, ref string pkFieldName, ref bool isPkFieldIdentity)
        {
            TableColumn[] cols = ExtractTableSchema(type, ref tblName, ref pkFieldName, ref isPkFieldIdentity);
            TableHelper.CreateTable(connStr, tblName, cols, false);
        }

        private static TableColumn[] ExtractTableSchema(Type type, ref string tblName, ref string pkFieldName, ref bool isPkIdentity)
        {
            object[] atts =
                type.GetCustomAttributes(typeof(OrmClassAttribute), false);
            OrmClassAttribute classAtt = null;
            if (atts != null && atts.Length > 0)
            {
                foreach (object att in atts)
                {
                    if (att is OrmClassAttribute)
                    {
                        classAtt = (OrmClassAttribute)att;
                        break;
                    }
                }
            }
            if (classAtt == null)
                throw new Exception("Object must be decorated with OrmClass attribute in order to use ORM");

            tblName = classAtt.TableName;
            pkFieldName = classAtt.PkFieldName;
            isPkIdentity = classAtt.IsPkIdentityField;
            List<TableColumn> cols = new List<TableColumn>();
            FieldInfo[] fields = type.GetFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsPublic)
                    {
                        TableColumn col = new TableColumn();
                        col.AllowNull = true;
                        col.ColumnName = field.Name;
                        if (field.FieldType.IsEnum)
                        {
                            col.DbType = SqlDbType.Int;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(field.FieldType.ToString());
                        }
                        col.IsFK = false;
                        col.IsIdentity = false;
                        col.IsIndexed = false;
                        col.IsPK = false;
                        col.Ordinal = cols.Count;
                        col.Precision = 0;
                        col.Scale = 0;
                        col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(col.ColumnName, col.DbType, col.Size,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                                    break;
                                }
                            }
                        }

                        if (ormFieldAtt.Mappable)
                        {
                            if (!string.IsNullOrEmpty(ormFieldAtt.ColumnName))
                                col.ColumnName = ormFieldAtt.ColumnName;
                            col.DbType = ormFieldAtt.FieldDataType;
                            col.Size = ormFieldAtt.Size;
                            if (col.ColumnName.ToLower() == pkFieldName.ToLower())
                            {
                                col.IsPK = true;
                                col.IsIndexed = true;
                                if (isPkIdentity)
                                {
                                    col.IsIdentity = true;
                                }
                            }

                            cols.Add(col);
                        }
                    }
                }
            }

            PropertyInfo[] props = GetMappableProperties(type);
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    if (prop.CanRead && prop.CanWrite)
                    {
                        TableColumn col = new TableColumn();
                        col.AllowNull = true;
                        col.ColumnName = prop.Name;
                        if (prop.PropertyType.IsEnum)
                        {
                            col.DbType = SqlDbType.Int;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                        }
                        col.IsFK = false;
                        col.IsIdentity = false;
                        col.IsIndexed = false;
                        col.IsPK = false;
                        col.Ordinal = cols.Count;
                        col.Precision = 0;
                        col.Scale = 0;
                        col.Size = FieldDataType.GetFieldDefaultSize(col.DbType);
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(col.ColumnName, col.DbType, col.Size,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                                    break;
                                }
                            }
                        }

                        if (ormFieldAtt.Mappable)
                        {
                            if (!string.IsNullOrEmpty(ormFieldAtt.ColumnName))
                                col.ColumnName = ormFieldAtt.ColumnName;
                            col.DbType = ormFieldAtt.FieldDataType;
                            col.Size = ormFieldAtt.Size;
                            if (col.ColumnName.ToLower() == pkFieldName.ToLower())
                            {
                                col.IsPK = true;
                                col.IsIndexed = true;
                                if (isPkIdentity)
                                {
                                    col.IsIdentity = true;
                                }
                            }
                            //if (ormFieldAtt.SerializationMethod == OrmFieldSerializationMethod.BinarySerialization)
                            //{
                            //    col.DbType = SqlDbType.Text;
                            //}
                            cols.Add(col);
                        }
                    }
                }
            }
            return cols.ToArray();
        }

        private static FieldInfo[] GetMappableFields(Type type)
        {
            List<FieldInfo> fieldInfos = new List<FieldInfo>();
            FieldInfo[] fields = type.GetFields();
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo field in fields)
                {
                    if (field.IsPublic)
                    {
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(field.Name, SqlDbType.VarChar, 50,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                                    break;
                                }
                            }
                        }
                        if (ormFieldAtt.Mappable)
                        {
                            fieldInfos.Add(field);
                        }
                    }
                }
            }
            return fieldInfos.ToArray();
        }

        private static PropertyInfo[] GetMappableProperties(Type type)
        {
            List<PropertyInfo> propInfos = new List<PropertyInfo>();
            PropertyInfo[] props = type.GetProperties();
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    if (prop.CanRead && prop.CanWrite)
                    {
                        OrmFieldAttribute ormFieldAtt =
                            new OrmFieldAttribute(prop.Name, SqlDbType.VarChar, 50,
                                                  OrmFieldSerializationMethod.DefaultSerialization);
                        object[] fieldAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length > 0)
                        {
                            foreach (object fieldAtt in fieldAttrs)
                            {
                                if (fieldAtt is OrmFieldAttribute)
                                {
                                    ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                                    break;
                                }
                            }
                        }
                        if (ormFieldAtt.Mappable)
                        {
                            propInfos.Add(prop);
                        }
                    }
                }
            }
            return propInfos.ToArray();
        }

        private static MemberInfo GetPkFieldMemberInfo(Type type)
        {
            string pkFieldName = string.Empty;
            object[] classAttrs = type.GetCustomAttributes(typeof(OrmClassAttribute), false);
            if (classAttrs != null && classAttrs.Length == 1)
            {
                pkFieldName = ((OrmClassAttribute)classAttrs[0]).PkFieldName;
            }
            Debug.Assert(!string.IsNullOrEmpty(pkFieldName), "ORM class must be decorated by [OrmClassAttribute]");

            MemberInfo[] members = type.GetMembers();
            foreach (MemberInfo member in members)
            {
                if (member.Name == pkFieldName)
                    return member;
                else
                {
                    if (member.MemberType == MemberTypes.Field)
                    {
                        FieldInfo field = (FieldInfo)member;
                        object[] fieldAttrs = field.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (fieldAttrs != null && fieldAttrs.Length == 1)
                        {
                            OrmFieldAttribute fieldAtt = (OrmFieldAttribute)fieldAttrs[0];
                            if (fieldAtt.ColumnName == pkFieldName)
                            {
                                return field;
                            }
                        }
                    }
                    else if (member.MemberType == MemberTypes.Property)
                    {
                        PropertyInfo prop = (PropertyInfo)member;
                        object[] propAttrs = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                        if (propAttrs != null && propAttrs.Length == 1)
                        {
                            OrmFieldAttribute propAtt = (OrmFieldAttribute)propAttrs[0];
                            if (propAtt.ColumnName == pkFieldName)
                            {
                                return prop;
                            }
                        }
                    }
                }
            }
            return null;
        }
        #endregion

        #region serialization
        private static string SerialzieFieldValue(Type type, string fieldName, object fieldValue)
        {
            Type fieldType = typeof(string);
            OrmFieldAttribute ormFieldAtt =
                new OrmFieldAttribute(fieldName, SqlDbType.VarChar, 50, OrmFieldSerializationMethod.DefaultSerialization);
            FieldInfo fieldInfo = type.GetField(fieldName);
            if (fieldInfo != null)
            {
                fieldType = fieldInfo.FieldType;
                ormFieldAtt.FieldDataType = FieldDataType.ToDbType(fieldInfo.FieldType.ToString());
                if (fieldInfo.FieldType.IsEnum)
                {
                    fieldValue = (int)fieldValue;
                    ormFieldAtt.FieldDataType = SqlDbType.Int;
                }
                ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);

                object[] fieldAtts = fieldInfo.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                if (fieldAtts != null && fieldAtts.Length > 0)
                {
                    foreach (object fieldAtt in fieldAtts)
                    {
                        if (fieldAtt is OrmFieldAttribute)
                        {
                            ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                            break;
                        }
                    }
                }
            }
            else
            {
                PropertyInfo prop = type.GetProperty(fieldName);
                if (prop != null)
                {
                    fieldType = prop.PropertyType;
                    ormFieldAtt.FieldDataType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    if (prop.PropertyType.IsEnum)
                    {
                        ormFieldAtt.FieldDataType = SqlDbType.Int;
                        fieldValue = (int)fieldValue;
                    }
                    ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);
                    object[] propAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propAtts != null && propAtts.Length > 0)
                    {
                        foreach (object propAtt in propAtts)
                        {
                            if (propAtt is OrmFieldAttribute)
                            {
                                ormFieldAtt = (OrmFieldAttribute)propAtt;
                                break;
                            }
                        }
                    }
                }
                else
                {
                    throw new Exception("Unable to find matching member for field " + fieldName);
                }
            }
            string value = null;
            if (fieldValue != null && fieldValue.GetType() != fieldType && fieldValue.GetType().IsSubclassOf(fieldType))
            {
                fieldType = fieldValue.GetType();
            }

            switch (ormFieldAtt.SerializationMethod)
            {
                case OrmFieldSerializationMethod.DefaultSerialization:
                    value =
                        SimpleDataTypeUtil.ReadValueAsString(
                            SimpleDataTypeUtil.ToSimpleDataType(FieldDataType.ToSystemType(ormFieldAtt.FieldDataType)),
                            fieldValue);
                    break;
                case OrmFieldSerializationMethod.XmlSerialization:
                    XmlSerializer serializer = new XmlSerializer(fieldType);
                    StringBuilder buffer = new StringBuilder();
                    StringWriter sWriter = new StringWriter(buffer);
                    serializer.Serialize(sWriter, fieldValue);
                    sWriter.Flush();
                    value = buffer.ToString();


                    break;
                case OrmFieldSerializationMethod.BinarySerialization:
                    BinaryFormatter bSerializer = new BinaryFormatter();
                    string tmpFilePath = FileUtil.GetWindowsAppTempFilePath("ORMapping", "orm");
                    FileStream fs = File.OpenWrite(tmpFilePath);
                    bSerializer.Serialize(fs, fieldValue);
                    fs.Close();

                    fs = File.OpenRead(tmpFilePath);
                    byte[] contents = new byte[fs.Length];
                    fs.Read(contents, 0, contents.Length);
                    fs.Close();
                    value = Base64Util.EncodeAsBase64(contents);

                    File.Delete(tmpFilePath);


                    break;
                case OrmFieldSerializationMethod.CustomSerializationMethod:
                    throw new NotImplementedException();
                default:
                    break;
            }
            return value;
        }

        private static object InstantiateFieldValue(Type type, string fieldName, string fieldValue)
        {
            Type fieldType = typeof(string);
            OrmFieldAttribute ormFieldAtt =
                new OrmFieldAttribute(fieldName, SqlDbType.VarChar, 50, OrmFieldSerializationMethod.DefaultSerialization);
            FieldInfo fieldInfo = type.GetField(fieldName);
            if (fieldInfo != null)
            {
                fieldType = fieldInfo.FieldType;
                ormFieldAtt.FieldDataType = FieldDataType.ToDbType(fieldInfo.FieldType.ToString());
                if (fieldInfo.FieldType.IsEnum)
                {
                    ormFieldAtt.FieldDataType = SqlDbType.Int;
                }
                ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);

                object[] fieldAtts = fieldInfo.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                if (fieldAtts != null && fieldAtts.Length > 0)
                {
                    foreach (object fieldAtt in fieldAtts)
                    {
                        if (fieldAtt is OrmFieldAttribute)
                        {
                            ormFieldAtt = (OrmFieldAttribute)fieldAtt;
                            break;
                        }
                    }
                }
            }
            else
            {
                PropertyInfo prop = type.GetProperty(fieldName);
                if (prop != null)
                {
                    fieldType = prop.PropertyType;
                    ormFieldAtt.FieldDataType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    if (prop.PropertyType.IsEnum)
                        ormFieldAtt.FieldDataType = SqlDbType.Int;
                    ormFieldAtt.Size = FieldDataType.GetFieldDefaultSize(ormFieldAtt.FieldDataType);
                    object[] propAtts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (propAtts != null && propAtts.Length > 0)
                    {
                        foreach (object propAtt in propAtts)
                        {
                            if (propAtt is OrmFieldAttribute)
                            {
                                ormFieldAtt = (OrmFieldAttribute)propAtt;
                                break;
                            }
                        }
                    }
                }
                else
                {
                    throw new Exception("Unable to find matching member for field " + fieldName);
                }
            }
            object value = null;
            if (fieldValue != null && fieldValue.GetType() != fieldType && fieldValue.GetType().IsSubclassOf(fieldType))
            {
                fieldType = fieldValue.GetType();
            }

            switch (ormFieldAtt.SerializationMethod)
            {
                case OrmFieldSerializationMethod.DefaultSerialization:
                    if (fieldType.IsEnum)
                    {
                        value = Enum.ToObject(fieldType, int.Parse(fieldValue));
                    }
                    else
                    {
                        value =
                            SimpleDataTypeUtil.ToDbValue(
                                SimpleDataTypeUtil.ToSimpleDataType(
                                    FieldDataType.ToSystemType(ormFieldAtt.FieldDataType)),
                                fieldValue);
                    }
                    break;
                case OrmFieldSerializationMethod.XmlSerialization:
                    XmlSerializer serializer = new XmlSerializer(fieldType);
                    StringReader sReader = new StringReader(fieldValue);
                    value = serializer.Deserialize(sReader);
                    break;
                case OrmFieldSerializationMethod.BinarySerialization:
                    BinaryFormatter bSerializer2 = new BinaryFormatter();
                    string tmpFilePath = FileUtil.GetWindowsAppTempFilePath("ORMapping", "orm");
                    FileStream fs2 = File.OpenWrite(tmpFilePath);
                    byte[] contents2 = Base64Util.DecodeAsBytes(fieldValue);
                    fs2.Write(contents2, 0, contents2.Length);
                    fs2.Flush();
                    fs2.Close();
                    fs2 = File.OpenRead(tmpFilePath);
                    value = bSerializer2.Deserialize(fs2);
                    fs2.Close();
                    File.Delete(tmpFilePath);
                    break;
                case OrmFieldSerializationMethod.CustomSerializationMethod:
                    throw new NotImplementedException();
                default:
                    break;
            }
            return value;
        }

        /// <summary>get field value from object</summary>
        private static string GetSerializedObjFieldValue(object obj, string colName)
        {
            FieldInfo[] fields = GetMappableFields(obj.GetType());
            if (fields != null && fields.Length > 0)
            {
                foreach (FieldInfo fi in fields)
                {
                    SqlDbType dbType = FieldDataType.ToDbType(fi.FieldType.ToString());
                    OrmFieldAttribute fieldAtt = new OrmFieldAttribute(fi.Name,
                        dbType, FieldDataType.GetFieldDefaultSize(dbType),
                        OrmFieldSerializationMethod.DefaultSerialization);
                    object[] atts = fi.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (atts != null && atts.Length > 0)
                    {
                        foreach (object att in atts)
                        {
                            if (att is OrmFieldAttribute)
                            {
                                fieldAtt = (OrmFieldAttribute)att;
                                break;
                            }
                        }
                    }
                    if (fieldAtt.ColumnName.ToLower() == colName.ToLower())
                    {
                        object val = fi.GetValue(obj);
                        if (val != null)
                        {
                            return SerialzieFieldValue(obj.GetType(), fi.Name, val);
                        }
                    }
                }
            }
            PropertyInfo[] props = GetMappableProperties(obj.GetType());
            if (props != null && props.Length > 0)
            {
                foreach (PropertyInfo prop in props)
                {
                    SqlDbType dbType = FieldDataType.ToDbType(prop.PropertyType.ToString());
                    OrmFieldAttribute fieldAtt = new OrmFieldAttribute(prop.Name,
                        dbType, FieldDataType.GetFieldDefaultSize(dbType),
                        OrmFieldSerializationMethod.DefaultSerialization);
                    object[] atts = prop.GetCustomAttributes(typeof(OrmFieldAttribute), false);
                    if (atts != null && atts.Length > 0)
                    {
                        foreach (object att in atts)
                        {
                            if (att is OrmFieldAttribute)
                            {
                                fieldAtt = (OrmFieldAttribute)att;
                                break;
                            }
                        }
                    }
                    if (fieldAtt.ColumnName.ToLower() == colName.ToLower())
                    {
                        object val = prop.GetValue(obj, null);
                        if (val != null)
                        {
                            return SerialzieFieldValue(obj.GetType(), prop.Name, val);
                        }
                    }
                }
            }

            return null;
        }
        #endregion
    }
}
